# -*- coding: UTF-8 -*-
"""
Course:        CS408 Computer Science Project B, TEAM 1
Semester:      2010 SPRING
Project:       DOHN(Web-based Account Book System)

File:          reports.py
Description:   Report package for report view.
Class:         ReportView, ReportController
"""


import sessions
import db
import views
import re
import string

import utils


class ReportView:
    """
    """
    def show_index_window(self, req):
        """
        @author: Sangyong Gil
        @summary: Index Window of Report page
        @param req: Request 
        """
        month1 = utils.get_current_month()
        month2 = utils.get_current_month()
        return self.show_periodical_window(req, month1, month2)
    
    def show_periodical_window(self, req, period1, period2):
        """
        @author: Sangyong Gil
        @summary: Periodical report page
        @param req: Request 
        @param period1: start month of report
        @param period2: end month of report
        """
        # ----------------------------------------
        # Get YYYY/M
        report_obj = ReportController()
        in_src = report_obj.get_transaction_period(req)        
        src = utils.replace("periodic.html", in_src)
        
        report_obj = ReportController()
        graph = report_obj.process_periodical(req, period1, period2)
        #graph = "%s --- %s" % (period1, period2)
        result = re.sub("<!---GRAPH!--->", graph, src)
        return result
    
    def show_categorical_window(self, req, period1, period2):
        """
        @author: Sangyong Gil
        @summary: Categorical report page
        @param req: Request
        @param period1: start month of report
        @param period2: end month of report
        """
        # ----------------------------------------
        # Get YYYY/M
        report_obj = ReportController()
        in_src = report_obj.get_transaction_period(req)
        src = utils.replace("categorical.html", in_src)
        
        graph_data = report_obj.process_categorical(req, period1, period2)
        #graph = "%s --- %s" % (period1, period2)
        result = re.sub("<!---GRAPH!--->", graph_data[0], src)
        result = re.sub("<!---INCOME!--->", graph_data[1], result)
        result = re.sub("<!---EXPENSE!--->", graph_data[2], result)
        return result


class ReportController:
    """
    """
    def get_transaction_period(self, req):
        """
        @author: Sangyong Gil
        @summary: Generate "YYYY/M" list for the graph pages
        @param req: Request
        """
        session_id = sessions.get_id(req)
        query = "SELECT distinct(concat_ws('-', year(date),month(date))) " + \
                "FROM transaction_tb " + \
                "WHERE(user_id = '%s' AND deleted = '0') " % (session_id) + \
                "ORDER BY date desc"  # descending order
        data = db.run_query(query)
        
        in_src = []
        count = 0
        for atuple in data:
            if count == 0:
                in_src.append("""\t<option selected value="%s">%s</option>\n""" % (string.strip(str(atuple[0])),string.strip(str(atuple[0]))))
            else:
                in_src.append("""\t<option value="%s">%s</option>\n""" % (string.strip(str(atuple[0])),string.strip(str(atuple[0]))))
            count = 1    
            # end a line
        in_src = ''.join(in_src)
        return in_src
    
    def process_periodical(self, req, period1, period2):
        """
        @author: Sangyong Gil
        @summary: Generate data table for the periodical graph
        @param req: Request 
        @param period1: start month of report
        @param period2: end month of report
        """
        # Identify given period
        date1 = str(period1).split("-")
        year1 = int(date1[0])
        month1 = int(date1[1])
        date2 = str(period2).split("-")
        year2 = int(date2[0])
        month2 = int(date2[1])
        
        if year1 > year2:
            tmp = year1
            year1 = year2
            year2 = tmp
            tmp = month1
            month1 = month2
            month2 = tmp
        elif year1 == year2 and month1 > month2:
            tmp = month1
            month1 = month2
            month2 = tmp
        
        session_id = sessions.get_id(req)
        query = "SELECT distinct(concat_ws('-', year(date),month(date))) " + \
                "FROM transaction_tb " + \
                "WHERE(user_id = '%s' AND deleted = '0' AND date BETWEEN %d AND %d) " % (session_id, year1*10000+month1*100, year2*10000+month2*100+31) + \
                "ORDER BY date"  # descending order
        data = db.run_query(query)
        
        # Data for graph
        in_src = []
        count = 0
        
        for atuple in data:
            date = string.strip(str(atuple[0])).split("-")
            year1 = int(date[0])
            month1 = int(date[1])
            
            # get income & expense    
            session_id = sessions.get_id(req)
            query = "SELECT sum(amount) " + \
            "FROM transaction_tb " + \
            "WHERE(user_id = '%s' AND deleted = '0' AND amount > 0 AND date BETWEEN %d AND %d) " % (session_id, year1*10000+month1*100, year1*10000+month1*100+31)
            data = db.run_query(query)
            list_str = list(data[0])
            stringIncome = str(list_str)
            stringIncome = stringIncome.replace('[', "")
            stringIncome = stringIncome.replace(']', "")
            stringIncome = stringIncome.replace('.0', "")
            
            query = "SELECT sum(amount) " + \
            "FROM transaction_tb " + \
            "WHERE(user_id = '%s' AND deleted = '0' AND amount < 0 AND date BETWEEN %d AND %d) " % (session_id, year1*10000+month1*100, year1*10000+month1*100+31)
            data = db.run_query(query)
            list_str = list(data[0])
            stringExpenses = str(list_str)
            stringExpenses = stringExpenses.replace('[', "")
            stringExpenses = stringExpenses.replace(']', "")
            stringExpenses = stringExpenses.replace('.0', "")
            
            if stringIncome != "None" or stringExpenses != "None":
                if stringIncome != "None":
                    income = int(stringIncome)
                else:
                    income = 0
                if stringExpenses != "None":
                    expenses = int(stringExpenses)
                    expenses *= -1
                else:
                    expenses = 0
                
                in_src.append("data.addRows(1);\n")
                in_src.append("data.setValue(%d, 0, '%d/%d')\n;" % (count, year1, month1))
                in_src.append("data.setValue(%d, 1, %d);\n" % (count, income))
                in_src.append("data.setValue(%d, 2, %d);\n" % (count, expenses))
                count += 1
            
        in_src = ''.join(in_src)
        return in_src

    def process_categorical(self, req, period1, period2):
        """
        @author: Sangyong Gil
        @summary: Generate data table for the categorical report
        @param req: Request 
        @param period1: start month of report
        @param period2: end month of report
        """
        # Identify given period
        date1 = str(period1).split("-")
        year1 = int(date1[0])
        month1 = int(date1[1])
        date2 = str(period2).split("-")
        year2 = int(date2[0])
        month2 = int(date2[1])
        
        if year1 > year2:
            tmp = year1
            year1 = year2
            year2 = tmp
            tmp = month1
            month1 = month2
            month2 = tmp
        elif year1 == year2 and month1 > month2:
            tmp = month1
            month1 = month2
            month2 = tmp
        
        user_id = sessions.get_id(req)
        # Fetch category_tb ----------------------------------------
        query = "SELECT num, description FROM category_tb " + \
                "WHERE(user_id = '%s') ORDER BY `order`" % (user_id)
        data = db.run_query(query)
        
        # Data for graph
        graph_data = []
        graphHTML = []
        incomeHTML = []
        expenseHTML = []
        sumIncome = 0
        sumExpense = 0
        count1 = 0
        count2 = 0
        
        for atuple in data:
            category = string.strip(str(atuple[1]))
            
            query = "SELECT sum(amount) " + \
            "FROM transaction_tb " + \
            "WHERE(user_id = '%s' AND deleted = '0' AND amount > 0 AND date BETWEEN %d AND %d AND category = '%d') " % (user_id, year1*10000+month1*100, year2*10000+month2*100+31, int(atuple[0]))
            data = db.run_query(query)
            list_str = list(data[0])
            stringIncome = str(list_str)
            stringIncome = stringIncome.replace('[', "")
            stringIncome = stringIncome.replace(']', "")
            stringIncome = stringIncome.replace('.0', "")
            
            query = "SELECT sum(amount) " + \
            "FROM transaction_tb " + \
            "WHERE(user_id = '%s' AND deleted = '0' AND amount < 0 AND date BETWEEN %d AND %d AND category = '%d') " % (user_id, year1*10000+month1*100, year2*10000+month2*100+31, int(atuple[0]))
            data = db.run_query(query)
            list_str = list(data[0])
            stringExpenses = str(list_str)
            stringExpenses = stringExpenses.replace('[', "")
            stringExpenses = stringExpenses.replace(']', "")
            stringExpenses = stringExpenses.replace('.0', "")
            
            if stringIncome != "None" or stringExpenses != "None":
                if stringIncome != "None":
                    income = int(stringIncome)
                    graphHTML.append("data.addRows(1);\n")
                    graphHTML.append("data.setValue(%d, 0, '%s');\n" % (count1, category))
                    graphHTML.append("data.setValue(%d, 1, %d);\n;" % (count1, income))
                    incomeHTML.append("<tr ><td >%s</td><td>%d</td></tr>" % (category, income))
                    count1 += 1
                    sumIncome += income
                if stringExpenses != "None":
                    expenses = int(stringExpenses)
                    expenses *= -1
                    graphHTML.append("data2.addRows(1);\n")
                    graphHTML.append("data2.setValue(%d, 0, '%s');\n" % (count2, category))
                    graphHTML.append("data2.setValue(%d, 1, %d);\n" % (count2, expenses))
                    expenseHTML.append("<tr ><td >%s</td><td>%d</td></tr>" % (category, expenses))
                    sumExpense += expenses
                    count2 += 1
        
        # for transactions which have undefined category
        query = "SELECT sum(amount) " + \
        "FROM transaction_tb " + \
        "WHERE(user_id = '%s' AND deleted = '0' AND amount > 0 AND date BETWEEN %d AND %d AND category = '0') " % (user_id, year1*10000+month1*100, year2*10000+month2*100+31)
        data = db.run_query(query)
        list_str = list(data[0])
        stringIncome = str(list_str)
        stringIncome = stringIncome.replace('[', "")
        stringIncome = stringIncome.replace(']', "")
        stringIncome = stringIncome.replace('.0', "")
        
        query = "SELECT sum(amount) " + \
            "FROM transaction_tb " + \
            "WHERE(user_id = '%s' AND deleted = '0' AND amount < 0 AND date BETWEEN %d AND %d AND category = '0') " % (user_id, year1*10000+month1*100, year2*10000+month2*100+31)
        data = db.run_query(query)
        list_str = list(data[0])
        stringExpenses = str(list_str)
        stringExpenses = stringExpenses.replace('[', "")
        stringExpenses = stringExpenses.replace(']', "")
        stringExpenses = stringExpenses.replace('.0', "")
        
        if stringIncome != "None" or stringExpenses != "None":
            if stringIncome != "None":
                income = int(stringIncome)
                graphHTML.append("data.addRows(1);\n")
                graphHTML.append("data.setValue(%d, 0, 'Undefined');\n" % (count1))
                graphHTML.append("data.setValue(%d, 1, %d);\n;" % (count1, income))
                incomeHTML.append("<tr ><td >Undefined</td><td>%d</td></tr>" % (income))
                sumIncome += income
            if stringExpenses != "None":
                expenses = int(stringExpenses)
                expenses *= -1
                graphHTML.append("data2.addRows(1);\n")
                graphHTML.append("data2.setValue(%d, 0, 'Undefined');\n" % (count2))
                graphHTML.append("data2.setValue(%d, 1, %d);\n" % (count2, expenses))
                expenseHTML.append("<tr ><td >Undefined</td><td>%d</td></tr>" % (expenses))
                sumExpense += expenses
         
                    
        incomeHTML.append("<tfoot><td>Total Income</td><td>%d</td></tfoot>" % sumIncome)
        expenseHTML.append("<tfoot><td>Total Expense</td><td>%d</td></tfoot>" % sumExpense)
            
        graphHTML = ''.join(graphHTML)
        incomeHTML = ''.join(incomeHTML)
        expenseHTML = ''.join(expenseHTML)
        
        graph_data.append(graphHTML)
        graph_data.append(incomeHTML)
        graph_data.append(expenseHTML)
        
        return graph_data